//
//  WxLogin.swift
//  imprint
//
//  Created by 小柠檬🍋 on 2025/1/11.
//

import Foundation
import SwiftUI
import GRDB

class LocalDiaryImpl : DiaryProtocol {
    
    func queryHomeDiary(userId: String, startTime: Int64, endTime: Int64, keyword: String?, pageNo: Int, pageSize: Int) async throws -> [DiaryBean] {
        return try await DatabaseManager.shared.dbQueue.read { db in
            try DiaryBean
                .filter(Column("time") <= endTime && Column("time") >= startTime)
                .filter(Column("status") == IStatusType.NORMAL.rawValue)
                .filter(Column("roleType") == IRoleType.USER.rawValue)
                .order(Column("time").desc)
                .limit(pageSize, offset: pageNo * pageSize)
                .fetchAll(db)
        }
    }
    
    
    func queryDiaryAi(userId:String, roleId:String, keyword:String?, pageNo:Int, pageSize:Int) async throws -> [DiaryBean] {
        return try await DatabaseManager.shared.dbQueue.read { db in
            let searchPattern = "%\(keyword ?? "")%"
            return try DiaryBean
                .filter(Column("content").like(searchPattern))
                .filter(Column("roleId") == roleId)
                .filter(Column("status") == IStatusType.NORMAL.rawValue)
                .filter(Column("roleType") == IRoleType.BOT.rawValue)
                .order(Column("time").desc)
                .limit(pageSize, offset: pageNo * pageSize)
                .fetchAll(db)
        }
    }
    
    func queryStatisticsDiary(userId: String, curTime: TimeInterval, endTime: TimeInterval, isPicture: Bool) async throws -> [DiaryBean] {
        return try await DatabaseManager.shared.dbQueue.read { db in
            try DiaryBean
            // 合并时间范围条件（注意参数顺序）
                .filter(Column("time") <= curTime && Column("time") >= endTime)
                .filter(Column("status") == IStatusType.NORMAL.rawValue)
                .filter(Column("roleType") == IRoleType.USER.rawValue)
                .order(Column("time").desc)
                .fetchAll(db)
        }
    }
    
    func queryDiaryLately(userId: String, pageNo: Int, pageSize: Int) async throws -> [DiaryBean] {
        return try await DatabaseManager.shared.dbQueue.read { db in
            try DiaryBean
                .filter(Column("status") == IStatusType.NORMAL.rawValue)
                .filter(Column("roleType") == IRoleType.USER.rawValue)
                .order(Column("createTime").desc)
                .limit(pageSize, offset: pageNo * pageSize)
                .fetchAll(db)
        }
    }
    
    func queryDiaryUpdateAll(userId: String, pageNo: Int, pageSize: Int) async throws -> [DiaryBean] {
        return try await DatabaseManager.shared.dbQueue.read { db in
            try DiaryBean
                .filter(Column("status") == IStatusType.NORMAL.rawValue)
                .order(Column("updateTime").desc)
                .limit(pageSize, offset: pageNo * pageSize)
                .fetchAll(db)
        }
    }
    
    func queryTag(userId: String, tagId: String?, pageNo: Int, pageSize: Int) async throws -> [DiaryBean] {
        return try await DatabaseManager.shared.dbQueue.read { db in
            // 创建基础查询
            var request = DiaryBean.all()
            
            // 动态处理 tagId 条件
            if let tagId = tagId {
                request = request.filter(Column("tagId") == tagId)
            } else {
                // 当 tagId 为 nil 时查询 tagId IS NULL 的记录
                request = request.filter(Column("tagId") == nil)
            }
            
            // 执行链式查询
            return try request
                .filter(Column("status") == IStatusType.NORMAL.rawValue)
                .filter(Column("roleType") == IRoleType.USER.rawValue)
                .order(Column("time").desc)
                .limit(pageSize, offset: pageNo * pageSize)
                .fetchAll(db)
        }
    }
    
    func queryMemoryDiary(aiUserId: String?, userId: String, pageNo: Int, pageSize: Int) async throws -> [DiaryBean] {
        return try await DatabaseManager.shared.dbQueue.read { db in
            if aiUserId?.isEmpty == true {
                //我的未提取日记
                return try DiaryBean
                    .filter(Column("status") == IStatusType.NORMAL.rawValue)
                    .filter(Column("roleType") == IRoleType.USER.rawValue)
                    .filter(Column("memory") != 1 || Column("memory") == nil)
                    .order(Column("time").desc)
                    .limit(pageSize, offset: pageNo * pageSize)
                    .fetchAll(db)
            } else {
                //ai未提取日记
                return try DiaryBean
                    .filter(Column("roleId") == aiUserId)
                    .filter(Column("status") == IStatusType.NORMAL.rawValue)
                    .filter(Column("roleType") == IRoleType.BOT.rawValue)
                    .filter(Column("memory") != 1 || Column("memory") == nil)
                    .order(Column("time").desc)
                    .limit(pageSize, offset: pageNo * pageSize)
                    .fetchAll(db)
            }
         
        }
    }
    
    
    func saveDiary(bean: DiaryBean) async throws {
        let curTime = getMillisecond(try await getNetTime())
        try await DatabaseManager.shared.dbQueue.write { db in
            bean.modifySign = true
            bean.updateTime = curTime
            if (bean.objectId ?? "").isEmpty == true {
                bean.createTime = curTime
                bean.objectId = UUID().uuidString
                try bean.insert(db)
            } else {
                try bean.update(db)
                showLog("保存数据")
            }
        }
    }
    
    func delDiary(bean: DiaryBean) async throws {
        try await DatabaseManager.shared.dbQueue.write { db in
            try DiaryBean
                .filter(Column("objectId") == bean.objectId)
                .deleteAll(db)
        }
    }
    
    func saveCloudPicture(bean: CloudPictureBean) async throws {
        let curTime = getMillisecond(try await getNetTime())
        try await DatabaseFolderManager.shared.dbQueue.write { db in
            bean.modifySign = true
            bean.updateTime = curTime
            if (bean.objectId ?? "").isEmpty == true {
                bean.createTime = curTime
                bean.objectId = UUID().uuidString
                try bean.insert(db)
            } else {
                try bean.update(db)
                showLog("保存数据")
            }
        }
    }
    
    func delCloudPicture(bean: CloudPictureBean) async throws {
        try await DatabaseFolderManager.shared.dbQueue.write { db in
            try CloudPictureBean
                .filter(Column("objectId") == bean.objectId)
                .deleteAll(db)
        }
    }
    
    func queryPicture(diaryId: String, userId: String, limit: Int) async throws -> [CloudPictureBean] {
        return try await DatabaseFolderManager.shared.dbQueue.read { db in
            try CloudPictureBean
                .filter(Column("status") == IStatusType.NORMAL.rawValue)
                .filter(Column("diaryId") == diaryId)
                .order(Column("sort").asc)
                .fetchAll(db)
        }
    }
    
    func queryDiaryComment(diaryId: String, pageNo: Int, pageSize: Int) async throws -> [DiaryCommentBean] {
        return try await DatabaseManager.shared.dbQueue.read { db in
            try DiaryCommentBean
                .filter(Column("status") == IStatusType.NORMAL.rawValue)
                .filter(Column("diaryId") == diaryId)
                .limit(pageSize, offset: pageNo * pageSize)
                .order(Column("time").asc)
                .fetchAll(db)
        }
    }
    
    
    func queryDiaryComment(diaryId: String, aiRoleId: String, pageNo: Int, pageSize: Int) async throws -> [DiaryCommentBean] {
        return try await DatabaseManager.shared.dbQueue.read { db in
            try DiaryCommentBean
                .filter(Column("status") == IStatusType.NORMAL.rawValue)
                .filter(Column("diaryId") == diaryId)
                .filter(Column("roleId") == aiRoleId)
                .limit(pageSize, offset: pageNo * pageSize)
                .order(Column("time").asc)
                .fetchAll(db)
        }
    }
    
    func queryDiaryLast(diaryId: String,aiRoleId: String)  async throws -> DiaryCommentBean? {
        return try await DatabaseManager.shared.dbQueue.read { db in
            let list = try DiaryCommentBean
                .filter(Column("status") == IStatusType.NORMAL.rawValue)
                .filter(Column("diaryId") == diaryId)
                .filter(Column("roleId") == aiRoleId)
                .limit(1)
                .order(Column("time").desc)
                .fetchAll(db)
            if list.isEmpty {
                return nil
            } else {
                return list[0]
            }
        }
    }
    
    func addDiaryComment(bean: DiaryCommentBean) async throws -> DiaryCommentBean? {
        let curTime = getMillisecond(try await getNetTime())
        return try await DatabaseManager.shared.dbQueue.write { db in
            bean.modifySign = true
            bean.updateTime = curTime
            if (bean.objectId ?? "").isEmpty == true {
                bean.createTime = curTime
                bean.objectId = UUID().uuidString
                try bean.insert(db)
            } else {
                try bean.update(db)
            }
            return bean
        }
    }
    
    func deleteDiaryComment(bean: DiaryCommentBean) async throws {
        try await DatabaseManager.shared.dbQueue.write { db in
            try DiaryCommentBean
                .filter(Column("objectId") == bean.objectId)
                .deleteAll(db)
        }
    }
    
    func queryWeekLetter(userId: String, pageNo: Int, pageSize: Int) async throws -> [WeeklyLetterBean] {
        return try await DatabaseManager.shared.dbQueue.read { db in
            try WeeklyLetterBean
                .filter(Column("status") == IStatusType.NORMAL.rawValue)
                .limit(pageSize, offset: pageNo * pageSize)
                .order(Column("time").desc)
                .fetchAll(db)
        }
    }
    
    func updateLastWeekLetter(bean: WeeklyLetterBean) async throws -> WeeklyLetterBean {
        let curTime = getMillisecond(try await getNetTime())
        try await DatabaseManager.shared.dbQueue.write { db in
            bean.modifySign = true
            bean.updateTime = curTime
            if (bean.objectId ?? "").isEmpty == true {
                bean.createTime = curTime
                bean.objectId = UUID().uuidString
                try bean.insert(db)
            } else {
                try bean.update(db)
                showLog("保存数据")
            }
        }
        return bean
    }
    
    func deleteLastWeekLetter(bean: WeeklyLetterBean) async throws {
        try await DatabaseManager.shared.dbQueue.write { db in
            try WeeklyLetterBean
                .filter(Column("objectId") == bean.objectId)
                .deleteAll(db)
        }
    }
    
    func queryActiveCall(userId: String, pageNo: Int, pageSize: Int) async throws -> [CallHistoryBean] {
        return try await DatabaseManager.shared.dbQueue.read { db in
            try CallHistoryBean
                .filter(Column("status") == IStatusType.NORMAL.rawValue)
                .limit(pageSize, offset: pageNo * pageSize)
                .order(Column("time").desc)
                .fetchAll(db)
        }
    }
    
    func deleteActiveCall(bean: CallHistoryBean) async throws {
        try await DatabaseManager.shared.dbQueue.write { db in
            try CallHistoryBean
                .filter(Column("objectId") == bean.objectId)
                .deleteAll(db)
        }
    }
    
    func updateActiveCall(bean: CallHistoryBean) async throws {
        let curTime = getMillisecond(try await getNetTime())
        try await DatabaseManager.shared.dbQueue.write { db in
            bean.modifySign = true
            bean.updateTime = curTime
            if (bean.objectId ?? "").isEmpty == true {
                bean.createTime = curTime
                bean.objectId = UUID().uuidString
                try bean.insert(db)
            } else {
                try bean.update(db)
                showLog("保存数据")
            }
        }
    }
    
    func existTask(timeKey: String, roleId: String?, userId: String, type: Int) async throws -> Bool {
        return try await DatabaseManager.shared.dbQueue.read { db in
            if (roleId ?? "").isEmpty {
                try TaskBean
                    .filter(Column("timeKey") == timeKey)
                    .filter(Column("type") == type)
                    .fetchCount(db)
            } else {
                try TaskBean
                    .filter(Column("timeKey") == timeKey)
                    .filter(Column("roleId") == roleId)
                    .filter(Column("type") == type)
                    .fetchCount(db)
            }
        } > 0
    }
    
    func saveTask(bean: TaskBean) async throws -> TaskBean? {
        try await DatabaseManager.shared.dbQueue.write { db in
            if (bean.objectId ?? "").isEmpty == true {
                bean.objectId = UUID().uuidString
                try bean.insert(db)
            } else {
                try bean.update(db)
                showLog("保存数据")
            }
        }
        return bean
    }
    func getLatelyTask(roleId: String?, userId: String, type: Int, pageSize: Int) async throws -> [TaskBean] {
        let curTime = getMillisecond(try await getNetTime())
        return try await DatabaseManager.shared.dbQueue.read { db in
            if (roleId ?? "").isEmpty {
                try TaskBean
                    .filter(Column("status") == IStatusType.NORMAL.rawValue)
                    .filter(Column("execute") == 0)
                    .filter(Column("type") == type)
                    .filter(Column("time") <= curTime)
                    .limit(pageSize)
                    .order(Column("time").asc)
                    .fetchAll(db)
            } else {
                try TaskBean
                    .filter(Column("status") == IStatusType.NORMAL.rawValue)
                    .filter(Column("execute") == 0)
                    .filter(Column("type") == type)
                    .filter(Column("roleId") == roleId)
                    .filter(Column("time") <= curTime)
                    .limit(pageSize)
                    .order(Column("time").asc)
                    .fetchAll(db)
            }
            
        }
    }
    func getUnExecutedTask(roleId: String?, userId: String, type: Int?) async throws -> [TaskBean] {
        return try await DatabaseManager.shared.dbQueue.read { db in
            if (roleId ?? "").isEmpty {
                if (type == nil) {
                    try TaskBean
                        .filter(Column("status") == IStatusType.NORMAL.rawValue)
                        .filter(Column("execute") == 0)
                        .order(Column("time").asc)
                        .fetchAll(db)
                } else {
                    try TaskBean
                        .filter(Column("status") == IStatusType.NORMAL.rawValue)
                        .filter(Column("execute") == 0)
                        .filter(Column("type") == type)
                        .order(Column("time").asc)
                        .fetchAll(db)
                }
                
            } else {
                if (type == nil) {
                    try TaskBean
                        .filter(Column("status") == IStatusType.NORMAL.rawValue)
                        .filter(Column("execute") == 0)
                        .filter(Column("roleId") == roleId)
                        .order(Column("time").asc)
                        .fetchAll(db)
                } else {
                    try TaskBean
                        .filter(Column("status") == IStatusType.NORMAL.rawValue)
                        .filter(Column("execute") == 0)
                        .filter(Column("type") == type)
                        .filter(Column("roleId") == roleId)
                        .order(Column("time").asc)
                        .fetchAll(db)
                }
                
            }
            
        }
    }
    func deleteTask(bean: TaskBean) async throws {
        try await DatabaseManager.shared.dbQueue.write { db in
            try TaskBean
                .filter(Column("objectId") == bean.objectId)
                .deleteAll(db)
        }
    }
    
    func queryChat(objectId: String) async throws -> ChatBean? {
        return try await DatabaseManager.shared.dbQueue.read { db in
            let list = try ChatBean
                .filter(Column("objectId") == objectId)
                .limit(1)
                .order(Column("time").desc)
                .fetchAll(db)
            if list.isEmpty {
                return nil
            } else {
                return list[0]
            }
        }
    }
    
    func queryChat(pageNo: Int, pageSize: Int) async throws -> [ChatBean] {
        return try await DatabaseManager.shared.dbQueue.read { db in
            try ChatBean
                .filter(Column("status") == IStatusType.NORMAL.rawValue)
                .limit(pageSize, offset: pageNo * pageSize)
                .order(Column("time").desc)
                .fetchAll(db)
        }
    }
    
    func queryChat(aiUserId: String, userId: String, pageNo: Int, pageSize: Int) async throws -> [ChatBean] {
        return try await DatabaseManager.shared.dbQueue.read { db in
            try ChatBean
                .filter(Column("status") == IStatusType.NORMAL.rawValue)
                .filter(Column("aiUserId") == aiUserId)
                .filter(Column("userId") == userId)
                .limit(pageSize, offset: pageNo * pageSize)
                .order(Column("time").desc)
                .fetchAll(db)
        }
    }
    func queryChat(aiUserId: String, userId: String, isRefresh: Bool, timestamp: Int64, pageSize: Int) async throws -> [ChatBean] {
        return try await DatabaseManager.shared.dbQueue.read { db in
            try ChatBean
                .filter(Column("status") == IStatusType.NORMAL.rawValue)
                .filter(Column("aiUserId") == aiUserId)
                .filter(Column("userId") == userId)
            // 根据isRefresh决定时间筛选条件
                .filter(isRefresh ? Column("time") < timestamp : Column("time") > timestamp)
                .order(Column("time").desc)
                .limit(pageSize)
                .fetchAll(db)
        }
    }
    
    func queryNewChat(aiUserId: String, userId: String) async throws -> ChatBean? {
        let list =  try await queryChat(aiUserId: aiUserId, userId: userId, pageNo: 0, pageSize: 1)
        if list.isEmpty {
            return nil
        }
        let bean = list[0]
        return bean
    }
    
    func queryMemoryChat(aiUserId: String, userId: String, pageNo: Int, pageSize: Int) async throws -> [ChatBean] {
        return try await DatabaseManager.shared.dbQueue.read { db in
            try ChatBean
                .filter(Column("status") == IStatusType.NORMAL.rawValue)
                .filter(Column("aiUserId") == aiUserId)
                .filter(Column("userId") == userId)
                .filter(Column("memory") != 1 || Column("memory") == nil)
                .limit(pageSize, offset: pageNo * pageSize)
                .order(Column("time").desc)
                .fetchAll(db)
        }
    }
    
    func addChat(bean: ChatBean) async throws -> ChatBean {
        bean.modifySign = true
        bean.objectId = UUID().uuidString
        let time = Date().timeIntervalSince1970
        bean.createTime = getMillisecond(time)
        bean.updateTime = getMillisecond(time)
        try await DatabaseManager.shared.dbQueue.write { db in
            try bean.insert(db)
        }
        return bean
    }
    
    func saveChat(bean: ChatBean) async throws {
        try await DatabaseManager.shared.dbQueue.write { db in
            try bean.update(db)
        }
    }
    
    func saveChat(list: [ChatBean]) async throws {
        try await DatabaseManager.shared.dbQueue.write { db in
            try list.forEach {
                try $0.update(db)
            }
        }
    }
    
    func clearChat(aiUserId: String, userId: String) async throws {
        try await DatabaseManager.shared.dbQueue.write { db in
            try ChatBean
                .filter(Column("aiUserId") == aiUserId)
                .filter(Column("userId") == userId)
                .deleteAll(db)
        }
    }
    
    func deleteChat(bean: ChatBean) async throws {
        try await DatabaseManager.shared.dbQueue.write { db in
            try ChatBean
                .filter(Column("objectId") == bean.objectId)
                .deleteAll(db)
        }
    }
    
    func deleteChatList(beans: [ChatBean]) async throws {
        let objectIds = beans.map { $0.objectId }

          try await DatabaseManager.shared.dbQueue.write { db in
              try ChatBean
                  .filter(objectIds.contains(Column("objectId")))
                  .deleteAll(db)
          }
    }
    
    func getEmojiPictureList() async throws -> [EmojiPictureBean] {
        return try await DatabaseManager.shared.dbQueue.read { db in
            try EmojiPictureBean
                .filter(Column("text") != nil)
                .filter(Column("text") != "")
                .order(Column("sort").asc)
                .fetchAll(db)
        }
    }
    
    func addEmojiPicture(bean: EmojiPictureBean) async throws -> EmojiPictureBean {
        bean.objectId = UUID().uuidString
        try await DatabaseManager.shared.dbQueue.write { db in
            try bean.insert(db)
        }
        return bean
    }
    
    func saveEmojiPicture(bean: EmojiPictureBean) async throws {
        try await DatabaseManager.shared.dbQueue.write { db in
            try bean.update(db)
        }
    }
    
    func deleteEmojiPicture(bean: EmojiPictureBean) async throws {
        try await DatabaseManager.shared.dbQueue.write { db in
            try EmojiPictureBean
                .filter(Column("objectId") == bean.objectId)
                .deleteAll(db)
        }
    }
    
    func queryNewChatMemory(aiRoleId: String, userId: String) async throws -> [AiMemoryBean] {
        let pageNo = 0
        let pageSize = 50
        return try await DatabaseManager.shared.dbQueue.read { db in
            try AiMemoryBean
                .filter(Column("status") == IStatusType.NORMAL.rawValue)
                .filter(Column("roleType") == IRoleType.BOT.rawValue)
                .filter(Column("aiRoleId") == aiRoleId)
                .order(Column("createTime").desc)
                .limit(pageSize, offset: pageNo * pageSize)
                .fetchAll(db)
        }
    }
    
    func queryMyNewChatMemory(userId: String) async throws -> [AiMemoryBean] {
        let pageNo = 0
        let pageSize = 50
        return try await DatabaseManager.shared.dbQueue.read { db in
            try AiMemoryBean
                .filter(Column("status") == IStatusType.NORMAL.rawValue)
                .filter(Column("roleType") == IRoleType.USER.rawValue)
                .order(Column("createTime").desc)
                .limit(pageSize, offset: pageNo * pageSize)
                .fetchAll(db)
        }
    }
    
    func addMemory(bean: AiMemoryBean) async throws {
        bean.objectId = UUID().uuidString
        try await DatabaseManager.shared.dbQueue.write { db in
            try bean.insert(db)
        }
    }
}
